create database [LabourManagement]
go
use [LabourManagement]
go
-------------------------------------------------------
create table Manager
(
	[ManagerID] int identity,
	[FullName] nvarchar(100),
	[UserName] nvarchar(100),
	[Password] nvarchar(100),
	constraint [PK_ManagerID] primary key (ManagerID)
)
go
-------------------------------------------------------
create table Labor
(
	[LaborID] nvarchar(10) not null,
	[FullName] nvarchar(100),
	[Address] nvarchar(100),
	[PhoneNumber] int,
	[Status] nvarchar(100),
	[Weight] nvarchar(100),
	[Height] nvarchar(100),
	[Gender] bit,
	[FamilyDetail] nvarchar(100),
	[PersonRefer] nvarchar(100),
	[Experience] nvarchar(100),
	[SalaryGrade] nvarchar(10) not null,
	[Building] nvarchar(100),
	[Permanent/Temporary] bit,
	constraint [PK_LaborID] primary key ([LaborID])
)
go
-------------------------------------------------------
create table Salary
(
	[SalaryGrade] nvarchar(10) not null,
	[SalaryPerHour] float,
	[Description] nvarchar(100)
	constraint [PK_SalaryGrade] primary key ([SalaryGrade])
)
go
-------------------------------------------------------
create table Attendance
(
	[LaborID] nvarchar(10) not null,
	[Day] date,
	[DailyAttendance] bit,
	[ShiftID] nvarchar(10) not null,
	[ExtraShift] int,
	[SalaryDaily] float
	 
	constraint [PK_Attendance] primary key ([LaborID],[Day],[ShiftID])
)
go
-------------------------------------------------------	

-------------------------------------------------------
create table Shift
(
	[ShiftID] nvarchar(10) not null,
	[ShiftName] nvarchar(100),
	[TimeIn] nvarchar(100),
	[TimeOut] nvarchar(100),
	[Duration] float,
	constraint PK_Shift primary key ([ShiftID])
)
go
-------------------------------------------------------

alter table Labor add constraint [FK_SalaryGrade] foreign key ([SalaryGrade]) references Salary([SalaryGrade])
go
alter table Attendance add constraint [FK_LaborID] foreign key ([LaborID]) references Labor([LaborID])
go
alter table Attendance add constraint [FK_ShiftID] foreign key ([ShiftID]) references Shift([ShiftID])
go



	--DROP DATABASE [LabourManagement]
